"""
债券数据访问层 (DAO)
提供债券相关数据的专用访问接口
"""

from typing import List, Optional, Dict, Any
from datetime import datetime, date, timedelta
from sqlalchemy import select, and_, or_, desc, asc
from sqlalchemy.ext.asyncio import AsyncSession

from .base import FinancialDataDAO
from ..models.bond import (
    BondBasicInfo,
    BondDailyData,
    BondConvertibleData,
    BondYieldData,
    BondIssueData
)


class BondBasicInfoDAO(FinancialDataDAO[BondBasicInfo]):
    """债券基本信息数据访问层"""
    
    model = BondBasicInfo
    
    async def get_by_bond_code(self, session: AsyncSession, bond_code: str) -> Optional[BondBasicInfo]:
        """根据债券代码获取债券基本信息"""
        stmt = select(self.model).where(self.model.bond_code == bond_code)
        result = await session.execute(stmt)
        return result.scalar_one_or_none()
    
    async def get_by_bond_type(self, session: AsyncSession, bond_type: str) -> List[BondBasicInfo]:
        """根据债券类型获取债券列表"""
        stmt = select(self.model).where(self.model.bond_type == bond_type)
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_by_issuer(self, session: AsyncSession, issuer: str) -> List[BondBasicInfo]:
        """根据发行人获取债券列表"""
        stmt = select(self.model).where(self.model.issuer == issuer)
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_active_bonds(self, session: AsyncSession) -> List[BondBasicInfo]:
        """获取活跃债券列表（未到期且状态正常）"""
        current_date = datetime.now().date()
        stmt = select(self.model).where(
            and_(
                or_(self.model.maturity_date.is_(None), self.model.maturity_date > current_date),
                self.model.status == 'active'
            )
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def search_bonds(
        self,
        session: AsyncSession,
        keyword: str,
        bond_type: Optional[str] = None,
        rating_min: Optional[str] = None
    ) -> List[BondBasicInfo]:
        """搜索债券（支持名称、代码、发行人模糊搜索）"""
        conditions = [
            or_(
                self.model.bond_name.ilike(f'%{keyword}%'),
                self.model.bond_code.ilike(f'%{keyword}%'),
                self.model.issuer.ilike(f'%{keyword}%')
            )
        ]
        
        if bond_type:
            conditions.append(self.model.bond_type == bond_type)
        
        if rating_min:
            # 简化的评级比较，实际应用中需要更复杂的评级排序逻辑
            conditions.append(self.model.credit_rating >= rating_min)
        
        stmt = select(self.model).where(and_(*conditions))
        result = await session.execute(stmt)
        return list(result.scalars().all())


class BondDailyDataDAO(FinancialDataDAO[BondDailyData]):
    """债券日行情数据访问层"""
    
    model = BondDailyData
    
    async def get_by_bond_and_date(
        self,
        session: AsyncSession,
        bond_code: str,
        trade_date: date
    ) -> Optional[BondDailyData]:
        """获取指定债券指定日期的行情数据"""
        stmt = select(self.model).where(
            and_(
                self.model.bond_code == bond_code,
                self.model.trade_date == trade_date
            )
        )
        result = await session.execute(stmt)
        return result.scalar_one_or_none()
    
    async def get_price_history(
        self,
        session: AsyncSession,
        bond_code: str,
        start_date: Optional[date] = None,
        end_date: Optional[date] = None,
        limit: int = 1000
    ) -> List[BondDailyData]:
        """获取债券价格历史数据"""
        conditions = [self.model.bond_code == bond_code]
        
        if start_date:
            conditions.append(self.model.trade_date >= start_date)
        if end_date:
            conditions.append(self.model.trade_date <= end_date)
        
        stmt = (
            select(self.model)
            .where(and_(*conditions))
            .order_by(desc(self.model.trade_date))
            .limit(limit)
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_recent_data(
        self,
        session: AsyncSession,
        bond_code: str,
        days: int = 30
    ) -> List[BondDailyData]:
        """获取最近N天的债券行情数据"""
        from_date = datetime.now().date() - timedelta(days=days)
        return await self.get_price_history(session, bond_code, from_date)
    
    async def get_yield_range_bonds(
        self,
        session: AsyncSession,
        min_yield: float,
        max_yield: float,
        trade_date: Optional[date] = None
    ) -> List[BondDailyData]:
        """获取收益率在指定范围内的债券"""
        conditions = [
            and_(
                self.model.yield_to_maturity >= min_yield,
                self.model.yield_to_maturity <= max_yield
            )
        ]
        
        if trade_date:
            conditions.append(self.model.trade_date == trade_date)
        else:
            # 获取最新交易日数据
            conditions.append(
                self.model.trade_date == select(self.model.trade_date).order_by(desc(self.model.trade_date)).limit(1).scalar_subquery()
            )
        
        stmt = select(self.model).where(and_(*conditions))
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_high_volume_trades(
        self,
        session: AsyncSession,
        min_volume: float,
        trade_date: Optional[date] = None
    ) -> List[BondDailyData]:
        """获取大额交易债券数据"""
        conditions = [self.model.volume >= min_volume]
        
        if trade_date:
            conditions.append(self.model.trade_date == trade_date)
        
        stmt = (
            select(self.model)
            .where(and_(*conditions))
            .order_by(desc(self.model.volume))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())


class BondConvertibleDataDAO(FinancialDataDAO[BondConvertibleData]):
    """可转债数据访问层"""
    
    model = BondConvertibleData
    
    async def get_by_bond_code(self, session: AsyncSession, bond_code: str) -> List[BondConvertibleData]:
        """获取可转债的所有数据记录"""
        stmt = (
            select(self.model)
            .where(self.model.bond_code == bond_code)
            .order_by(desc(self.model.data_date))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_by_stock_code(self, session: AsyncSession, stock_code: str) -> List[BondConvertibleData]:
        """根据正股代码获取可转债数据"""
        stmt = (
            select(self.model)
            .where(self.model.stock_code == stock_code)
            .order_by(desc(self.model.data_date))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_latest_data(self, session: AsyncSession, bond_code: str) -> Optional[BondConvertibleData]:
        """获取可转债的最新数据"""
        stmt = (
            select(self.model)
            .where(self.model.bond_code == bond_code)
            .order_by(desc(self.model.data_date))
            .limit(1)
        )
        result = await session.execute(stmt)
        return result.scalar_one_or_none()
    
    async def get_by_premium_range(
        self,
        session: AsyncSession,
        min_premium: float,
        max_premium: float,
        data_date: Optional[date] = None
    ) -> List[BondConvertibleData]:
        """获取转股溢价率在指定范围内的可转债"""
        conditions = [
            and_(
                self.model.convert_premium_ratio >= min_premium,
                self.model.convert_premium_ratio <= max_premium
            )
        ]
        
        if data_date:
            conditions.append(self.model.data_date == data_date)
        
        stmt = select(self.model).where(and_(*conditions))
        result = await session.execute(stmt)
        return list(result.scalars().all())


class BondYieldDataDAO(FinancialDataDAO[BondYieldData]):
    """债券收益率数据访问层"""
    
    model = BondYieldData
    
    async def get_yield_curve(
        self,
        session: AsyncSession,
        data_date: date,
        yield_type: Optional[str] = None,
        market_type: Optional[str] = None
    ) -> List[BondYieldData]:
        """获取指定日期的收益率曲线数据"""
        conditions = [self.model.data_date == data_date]
        
        if yield_type:
            conditions.append(self.model.yield_type == yield_type)
        if market_type:
            conditions.append(self.model.market_type == market_type)
        
        stmt = (
            select(self.model)
            .where(and_(*conditions))
            .order_by(asc(self.model.term))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_latest_yield_curve(
        self,
        session: AsyncSession,
        yield_type: Optional[str] = None,
        market_type: Optional[str] = None
    ) -> List[BondYieldData]:
        """获取最新的收益率曲线数据"""
        # 获取最新日期
        latest_date_stmt = select(self.model.data_date).order_by(desc(self.model.data_date)).limit(1)
        latest_date_result = await session.execute(latest_date_stmt)
        latest_date = latest_date_result.scalar_one_or_none()
        
        if not latest_date:
            return []
        
        return await self.get_yield_curve(session, latest_date, yield_type, market_type)
    
    async def get_yield_history(
        self,
        session: AsyncSession,
        term: float,
        yield_type: str,
        start_date: Optional[date] = None,
        end_date: Optional[date] = None,
        market_type: Optional[str] = None
    ) -> List[BondYieldData]:
        """获取指定期限的收益率历史数据"""
        conditions = [
            self.model.term == term,
            self.model.yield_type == yield_type
        ]
        
        if start_date:
            conditions.append(self.model.data_date >= start_date)
        if end_date:
            conditions.append(self.model.data_date <= end_date)
        if market_type:
            conditions.append(self.model.market_type == market_type)
        
        stmt = (
            select(self.model)
            .where(and_(*conditions))
            .order_by(desc(self.model.data_date))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def compare_yield_curves(
        self,
        session: AsyncSession,
        date1: date,
        date2: date,
        yield_type: Optional[str] = None,
        market_type: Optional[str] = None
    ) -> Dict[str, List[BondYieldData]]:
        """比较两个日期的收益率曲线"""
        curve1 = await self.get_yield_curve(session, date1, yield_type, market_type)
        curve2 = await self.get_yield_curve(session, date2, yield_type, market_type)
        
        return {
            'date1': curve1,
            'date2': curve2
        }


class BondIssueDataDAO(FinancialDataDAO[BondIssueData]):
    """债券发行数据访问层"""
    
    model = BondIssueData
    
    async def get_by_bond_code(self, session: AsyncSession, bond_code: str) -> Optional[BondIssueData]:
        """获取债券发行信息"""
        stmt = select(self.model).where(self.model.bond_code == bond_code)
        result = await session.execute(stmt)
        return result.scalar_one_or_none()
    
    async def get_by_underwriter(self, session: AsyncSession, underwriter: str) -> List[BondIssueData]:
        """获取承销商的所有债券发行记录"""
        stmt = select(self.model).where(self.model.lead_underwriter.ilike(f'%{underwriter}%'))
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_recent_issuances(
        self,
        session: AsyncSession,
        days: int = 30
    ) -> List[BondIssueData]:
        """获取最近发行的债券"""
        from_date = datetime.now().date() - timedelta(days=days)
        stmt = (
            select(self.model)
            .where(self.model.issue_start_date >= from_date)
            .order_by(desc(self.model.issue_start_date))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_by_issue_period(
        self,
        session: AsyncSession,
        start_date: date,
        end_date: date
    ) -> List[BondIssueData]:
        """获取指定时间段的债券发行数据"""
        stmt = (
            select(self.model)
            .where(
                and_(
                    self.model.issue_start_date >= start_date,
                    self.model.issue_start_date <= end_date
                )
            )
            .order_by(desc(self.model.issue_start_date))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_large_issuances(
        self,
        session: AsyncSession,
        min_amount: float,
        start_date: Optional[date] = None
    ) -> List[BondIssueData]:
        """获取大额债券发行数据"""
        conditions = [self.model.actual_issue_amount >= min_amount]
        
        if start_date:
            conditions.append(self.model.issue_start_date >= start_date)
        
        stmt = (
            select(self.model)
            .where(and_(*conditions))
            .order_by(desc(self.model.actual_issue_amount))
        )
        result = await session.execute(stmt)
        return list(result.scalars().all())
    
    async def get_issuance_statistics(
        self,
        session: AsyncSession,
        start_date: date,
        end_date: date
    ) -> Dict[str, Any]:
        """获取发行统计数据"""
        from sqlalchemy import func
        
        stmt = select(
            func.count(self.model.id).label('total_count'),
            func.sum(self.model.actual_issue_amount).label('total_amount'),
            func.avg(self.model.actual_issue_amount).label('avg_amount'),
            func.avg(self.model.issue_rate).label('avg_issue_rate')
        ).where(
            and_(
                self.model.issue_start_date >= start_date,
                self.model.issue_start_date <= end_date
            )
        )
        
        result = await session.execute(stmt)
        row = result.first()
        
        return {
            'total_count': row.total_count or 0,
            'total_amount': float(row.total_amount or 0),
            'avg_amount': float(row.avg_amount or 0),
            'avg_issue_rate': float(row.avg_issue_rate or 0)
        }